# -*- coding = UTF-8 -*-
import websocket
import json
import gzip
import _thread as thread
import time
import pandas as pd
from sqlalchemy import create_engine

websocket.enableTrace(True)
wsurl = "wss://api.huobiasia.vip/ws"  # 此地址内地正常访问


def on_message(ws, message):  # 接受信息处理
    msg = json.loads(gzip.decompress(message).decode("utf-8"))  # 信息gzip解压，json.loads转为json对象
    try:
        pong = msg['ping']  # huobi有心跳检测，需要返回ping的数值给服务器
        ws.send(json.dumps({"pong": pong}))
    except:
        df = pd.DataFrame(msg['data'])
        print(df)
        engine = create_engine('mysql+pymysql://root:password@localhost:3306/crypto')
        df.to_sql('btcusdt_kline_5min', engine, if_exists='append', index=False)  # 使用pandas的to_sql写入MySQL
        print('write to mysql...')


def on_error(ws, error):
    print("连接错误", error)


def on_open(ws):
    print('开始请求')

    def run():
        t = int(time.time())
        while t > 1577808000:  # 起始时间设定 2020.1.1
            t1 = t - 60 * 800  # 一次获取800条
            # print(t1,t)
            msg = {"req": "market.btcusdt.kline.1min", "id": "123", "from": t1, "to": t}
            t = t1
            ws.send(json.dumps(msg))
            time.sleep(1)

    thread.start_new_thread(run, ())


def on_close(ws):
    print("连接关闭")


if __name__ == "__main__":
    ws = websocket.WebSocketApp(wsurl, on_message=on_message, on_close=on_close, on_error=on_error)
    ws.on_open = on_open
    ws.run_forever()


# 建表
# CREATE TABLE btcusdt_kline_1min(
#     id int,
#     open float,
#     close float,
#     high float,
#     low float,
#     vol float,
#     amount float,
#     count float
# )